﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
//using Microsoft.Office.Interop.Excel;

namespace DrugsShopManagement.DBCLASS
{
    class Operator
    {
        
        SqlDataAdapter sda;

        SqlCommandBuilder scb;

        DataTable dt;

        DBCLASS.getSqlConnection myconnection = new DBCLASS.getSqlConnection();
        SqlConnection conn = null;

        private void Open()//打开数据库连接
        {
            if (conn == null)
            {
                conn = new SqlConnection(getSqlConnection.conStr);
            }
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        private void Close()//关闭连接
        {
            if (conn != null && conn.State != ConnectionState.Closed)
            {
                conn.Close();
            }
        }

        public void Dispose()//释放数据库连接
        {
            if (conn != null)
            {
                conn.Dispose();
                conn = null;
            }
        }

        public int ExecuteSql(string sqlstring)//执行SqlCommand命令
        {
            this.Open();
            SqlCommand com = new SqlCommand(sqlstring,conn);
            int n = com.ExecuteNonQuery();
            this.Close();
            this.Dispose();
            return n;
        }

        //public void ExecuteSql_drugOut(string sqlstring)//执行SqlCommand命令
        //{
        //    this.Open();
        //    SqlCommand com = new SqlCommand(sqlstring, conn);
        //    com.ExecuteNonQuery();
        //    this.Close();
        //    this.Dispose();
        //}

        public DataSet GetDataSet(string str_sqlstr, string str_table)//创建Dataset对象
        {
            this.Open();
            SqlDataAdapter sqlda = new SqlDataAdapter(str_sqlstr, conn);
            DataSet ds = new DataSet();
            sqlda.Fill(ds, str_table);
            this.Close();
            this.Dispose();
            return ds;
        }

        public DataSet getDataSet(string str_sqlstr)//创建DataSet对象--20120501cwm
        {
            this.Open();
            SqlDataAdapter sqlda = new SqlDataAdapter(str_sqlstr, conn);
            DataSet ds = new DataSet();
            sqlda.Fill(ds);
            this.Close();
            this.Dispose();
            return ds;
        }

        public DataTable getDataTable(string ssql)//抑郁添加20120608 10:57 获取供应商
        {
            DataTable dt=new DataTable();
            this.Open();
            SqlCommand cmd = new SqlCommand(ssql,conn);
            SqlDataReader sdr =cmd.ExecuteReader();
            dt.Load(sdr);
            sdr.Close();
            this.Close();
            return dt;
        }

        public void cboxBind(string str_sqlstr, string str_table, string str_tbMember, ComboBox cbox)//绑定ComboBox数据
        {
            this.Open();
            DataSet ds = GetDataSet(str_sqlstr, str_table);
            cbox.DataSource = ds.Tables[str_table];
            cbox.DisplayMember = str_tbMember;
            this.Close();
            this.Dispose();
        }

        public DataTable getDataTable(string str_sqlstr, string str_table)//创建DataTable对象
        {
            this.Open();
            sda = new SqlDataAdapter(str_sqlstr, conn);
            scb = new SqlCommandBuilder(sda);
            dt = new DataTable(str_table);
            sda.Fill(dt);
            this.Close();
            this.Dispose();
            return dt;
        }

        //public void execSql(string str_sql)
        //{
        //    DBCLASS.getSqlConnection myconnection = new DBCLASS.getSqlConnection();
        //    SqlConnection conn = myconnection.GetCon();
        //    da = new SqlCommand(str_sql,conn);
        //    da.ExecuteNonQuery();
        //}
        public string GetDataReader(string sqlstring)
        {
            this.Open();
            SqlCommand com = new SqlCommand(sqlstring,conn);
           SqlDataReader dr = com.ExecuteReader(CommandBehavior.CloseConnection);
            string s = dr[0].ToString();
            this.Close();
            this.Dispose();

            return s;
        }
        public void ExportData(DataGridView srcDgv, string fileName)//导出数据,传入一个datagridview和一个文件路径
        {
            string type = fileName.Substring(fileName.IndexOf(".") + 1);//获得数据类型
            if (type.Equals("xls", StringComparison.CurrentCultureIgnoreCase))//Excel文档
            {
                Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
                try
                {
                    excel.DisplayAlerts = false;
                    excel.Workbooks.Add(true);
                    excel.Visible = false;

                    for (int i = 0; i < srcDgv.Columns.Count; i++)//设置标题
                    {
                        excel.Cells[1, i + 1] = srcDgv.Columns[i].HeaderText;
                    }

                    for (int i = 0; i < srcDgv.Rows.Count; i++)//填充数据
                    {
                        for (int j = 0; j < srcDgv.Columns.Count; j++)
                        {
                            if (srcDgv[j, i].ValueType.ToString() == "System.Byte[]")
                            {
                                excel.Cells[i + 2, j + 1] = "System.Byte[]";
                            }
                            else
                            {
                                excel.Cells[i + 2, j + 1] = srcDgv[j, i].Value;
                            }
                        }
                    }

                    excel.Workbooks[1].SaveCopyAs(fileName);//保存
                }
                finally
                {
                    excel.Quit();
                }
                return;
            }
        }

        /// <summary> 
        /// 在指定的字符串列表CnStr中检索符合拼音索引字符串 
        /// </summary> 
        /// <param name="CnStr">汉字字符串</param> 
        /// <returns>相对应的汉语拼音首字母串</returns> 
        public static string GetSpellCode(string CnStr)
        {
            string strTemp = "";
            int iLen = CnStr.Length;
            int i = 0;

            for (i = 0; i <= iLen - 1; i++)
            {
                strTemp += GetCharSpellCode(CnStr.Substring(i, 1));
            }

            return strTemp;
        }


        /// <summary> 
        /// 得到一个汉字的拼音第一个字母，如果是一个英文字母则直接返回大写字母 
        /// </summary> 
        /// <param name="CnChar">单个汉字</param> 
        /// <returns>单个大写字母</returns> 
        private static string GetCharSpellCode(string CnChar)
        {
            long iCnChar;

            byte[] ZW = System.Text.Encoding.Default.GetBytes(CnChar);

            //如果是字母，则直接返回 
            if (ZW.Length == 1)
            {
                return CnChar.ToUpper();
            }
            else
            {
                // get the array of byte from the single char 
                int i1 = (short)(ZW[0]);
                int i2 = (short)(ZW[1]);
                iCnChar = i1 * 256 + i2;
            }

            //expresstion 
            //table of the constant list 
            // 'A'; //45217..45252 
            // 'B'; //45253..45760 
            // 'C'; //45761..46317 
            // 'D'; //46318..46825 
            // 'E'; //46826..47009 
            // 'F'; //47010..47296 
            // 'G'; //47297..47613 

            // 'H'; //47614..48118 
            // 'J'; //48119..49061 
            // 'K'; //49062..49323 
            // 'L'; //49324..49895 
            // 'M'; //49896..50370 
            // 'N'; //50371..50613 
            // 'O'; //50614..50621 
            // 'P'; //50622..50905 
            // 'Q'; //50906..51386 

            // 'R'; //51387..51445 
            // 'S'; //51446..52217 
            // 'T'; //52218..52697 
            //没有U,V 
            // 'W'; //52698..52979 
            // 'X'; //52980..53640 
            // 'Y'; //53689..54480 
            // 'Z'; //54481..55289 

            // iCnChar match the constant 
            if ((iCnChar >= 45217) && (iCnChar <= 45252))
            {
                return "A";
            }
            else if ((iCnChar >= 45253) && (iCnChar <= 45760))
            {
                return "B";
            }
            else if ((iCnChar >= 45761) && (iCnChar <= 46317))
            {
                return "C";
            }
            else if ((iCnChar >= 46318) && (iCnChar <= 46825))
            {
                return "D";
            }
            else if ((iCnChar >= 46826) && (iCnChar <= 47009))
            {
                return "E";
            }
            else if ((iCnChar >= 47010) && (iCnChar <= 47296))
            {
                return "F";
            }
            else if ((iCnChar >= 47297) && (iCnChar <= 47613))
            {
                return "G";
            }
            else if ((iCnChar >= 47614) && (iCnChar <= 48118))
            {
                return "H";
            }
            else if ((iCnChar >= 48119) && (iCnChar <= 49061))
            {
                return "J";
            }
            else if ((iCnChar >= 49062) && (iCnChar <= 49323))
            {
                return "K";
            }
            else if ((iCnChar >= 49324) && (iCnChar <= 49895))
            {
                return "L";
            }
            else if ((iCnChar >= 49896) && (iCnChar <= 50370))
            {
                return "M";
            }

            else if ((iCnChar >= 50371) && (iCnChar <= 50613))
            {
                return "N";
            }
            else if ((iCnChar >= 50614) && (iCnChar <= 50621))
            {
                return "O";
            }
            else if ((iCnChar >= 50622) && (iCnChar <= 50905))
            {
                return "P";
            }
            else if ((iCnChar >= 50906) && (iCnChar <= 51386))
            {
                return "Q";
            }
            else if ((iCnChar >= 51387) && (iCnChar <= 51445))
            {
                return "R";
            }
            else if ((iCnChar >= 51446) && (iCnChar <= 52217))
            {
                return "S";
            }
            else if ((iCnChar >= 52218) && (iCnChar <= 52697))
            {
                return "T";
            }
            else if ((iCnChar >= 52698) && (iCnChar <= 52979))
            {
                return "W";
            }
            else if ((iCnChar >= 52980) && (iCnChar <= 53640))
            {
                return "X";
            }
            else if ((iCnChar >= 53689) && (iCnChar <= 54480))
            {
                return "Y";
            }
            else if ((iCnChar >= 54481) && (iCnChar <= 55289))
            {
                return "Z";
            }
            else return ("?");
        }
    }
}
